﻿using com.nm.ApiServer.Attributes;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using VerticaAPI.model;
using System.Data;
using Nancy.Extensions;
using george.Web;
using com.nm.Cache;
using com.nm.Helper;
using com.nm.library;

namespace VerticaAPI
{
    [Description("区域信息")]
    public class OdsErpBasAreaHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        private readonly IList<string> STOCKLIST = new List<string>()
        {
            "'GDC'",
            "'XDC'",
            "'ZDDC'",
            "'YLC'",
            "'SXC'",
            "'TYC'",
            "'ZZC'",
            "'WHC'",
            "'CDC'",
            "'JBC'",
            "'LNC'",
            "'XAC'",
            "'HNC'",
            "'HNFP'"
        };

        #region 没SQL不用改

        #region 所有地区列表 GetAllOdsErpBasAreaList

        [MustLogin]
        [Description("所有地区列表")]
        [ReturnType(typeof(IList<IGrouping<string, OdsErpBasAreaModel.ListModel>>))]
        public ApiResult GetAllOdsErpBasAreaList()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }

            try
            {
                //var cacheKey = "all-AllOdsErpBasArea-list";
                //var result = RedisCache.GetAndDecodeJson<IList<IGrouping<string, OdsErpBasAreaModel.ListModel>>>(cacheKey);
                //if (result == null)
                //{
                var dt = ReturnAllOdsErpBasAreaList(0);
                var result =
                      ModelConvertHelper<OdsErpBasAreaModel.ListModel>.ConvertToModel(dt).OrderBy(d => d.AreaID)
                          .GroupBy(d => d.ParentID).OrderBy(d => d.Key)
                          .ToList();
                //    RedisCache.MaxString(cacheKey, result.ToJson());
                //}
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 待选地区列表 GetAllOdsBasAreaList

        [MustLogin]
        [Description("待选地区列表")]
        [ReturnType(typeof(IGrouping<string, OdsErpBasAreaModel.ListModel>))]
        public ApiResult GetAllOdsBasAreaList([ParamDescription("类型-1区域，2仓库")] int Param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var dt = ReturnAllOdsErpBasAreaList(Param);
                var result =
                    ModelConvertHelper<OdsErpBasAreaModel.ListModel>.ConvertToModel(dt).OrderBy(d => d.AreaID)
                        .GroupBy(d => d.ParentID).OrderBy(d => d.Key)
                        .ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 根据区域，仓库获取地区列表  GetOdsErpBasAreaList

        [MustLogin]
        [Description("根据区域，仓库获取地区列表")]
        [ReturnType(typeof(IGrouping<string, OdsErpBasAreaModel.ListModel>))]
        public ApiResult GetOdsErpBasAreaList([ParamDescription("类型-1区域，2仓库")]int Param, string DistrictID)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (string.IsNullOrEmpty(DistrictID)) return Error("参数不能为空！");
                var dt = ReturnChooseDataTable(Param, DistrictID);
                var result = ModelConvertHelper<OdsErpBasAreaModel.ListModel>.ConvertToModel(dt).OrderBy(d => d.AreaID)
                    .GroupBy(d => d.ParentID).OrderBy(d => d.Key);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        #endregion

        #region 根据区域，仓库获取地区列表 GetOdsErpBasAreaList2

        [MustLogin]
        [Description("根据区域，仓库获取地区列表")]
        [ReturnType(typeof(IGrouping<string, OdsErpBasAreaModel.ListModel>))]
        public ApiResult GetOdsErpBasAreaList2([ParamDescription("类型-1区域，2仓库")]int Param, string DistrictIDs)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (string.IsNullOrEmpty(DistrictIDs)) return Error("参数不能为空！");
                var dt = ReturnChooseDataTable2(Param, DistrictIDs);
                var result = ModelConvertHelper<OdsErpBasAreaModel.ListModel>.ConvertToModel(dt).OrderBy(d => d.AreaID)
                    .GroupBy(d => d.ParentID).OrderBy(d => d.Key);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 所有仓库列表 GetAllOdsErpBasStockList

        [MustLogin]
        [Description("所有仓库列表")]
        [ReturnType(typeof(IList<OdsErpBasStock.NeceModel>))]
        public ApiResult GetAllOdsErpBasStockList()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var cacheKey = $"all-AllOdsErpBasStock-{UserInfo.UserCompanyId}-list";
                var result = RedisCache.GetAndDecodeJson<IList<OdsErpBasStock.NeceModel>>(cacheKey);
                if (result == null)
                {
                    var dt = ReturnAllOdsErpBasStockList();
                    result = ModelConvertHelper<OdsErpBasStock.NeceModel>.ConvertToModel(dt);
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #endregion

        #region (ok)返回DataTable ReturnAllOdsErpBasAreaList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ReturnAllOdsErpBasAreaList(int param)
        {
            try
            {
                var sql = "";
                //地区
                if (param == 0)
                {
                    sql = $@" select distinct eba.*,ebap.AreaName as ParentName,
                            case when apc.AreaID is null or apc.StockID is null
                            then false
                            else true
                            end as IsChoose
                            from nods.nods_erp_Bas_Area eba
                            inner join nods.nods_erp_Bas_Area ebap on eba.ExtrSource = ebap.ExtrSource and eba.ParentID=ebap.AreaID
                            left join ndw.ndw_AreaProCity apc on eba.ExtrSource = apc.CompanyID and eba.AreaID=apc.AreaID
                            where  eba.ExtrSource = '{UserInfo.UserExtrSource}' and eba.AllowUsed=1 and eba.Level = 2
                            order by IsChoose desc ";
                }
                else if (param == 1)
                {
                    sql = $@" select distinct eba.*,ebap.AreaName as ParentName,
                            case when apc.AreaID is null or apc.StockID is null
                            then false
                            else true
                            end as IsChoose
                            from nods.nods_erp_Bas_Area eba
                            inner join nods.nods_erp_Bas_Area ebap on eba.ExtrSource = ebap.ExtrSource and eba.ParentID=ebap.AreaID
                            left join ndw.ndw_AreaProCity apc on eba.ExtrSource = apc.CompanyID and eba.AreaID=apc.AreaID
                            where   eba.ExtrSource = '{UserInfo.UserExtrSource}' and  eba.AllowUsed=1 and eba.Level = 2 and isnull(apc.ParentAreaID,'')=''
                            order by IsChoose desc ";
                }
                else
                {
                    sql = $@" select distinct eba.*,ebap.AreaName as ParentName,
                            case when apc.AreaID is null or apc.StockID is null
                            then false
                            else true
                            end as IsChoose
                            from nods.nods_erp_Bas_Area eba
                            inner join nods.nods_erp_Bas_Area ebap on eba.ExtrSource = ebap.ExtrSource and eba.ParentID=ebap.AreaID
                            left join ndw.ndw_AreaProCity apc on eba.ExtrSource = apc.CompanyID and eba.AreaID=apc.AreaID
                            where   eba.ExtrSource = '{UserInfo.UserExtrSource}' and  eba.AllowUsed=1 and eba.Level = 2 and isnull(apc.StockID,'')=''
                            order by IsChoose desc ";
                }
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnChooseDataTable2

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="DistrictID"></param>
        /// <param name="StockID"></param>
        /// <returns></returns>
        public DataTable ReturnChooseDataTable2(int param, string DistrictIDs)
        {
            try
            {
                var sql = $@" select distinct eba.*,ebap.AreaName as ParentName,true as IsChoose
                            from nods.nods_erp_Bas_Area eba
                            inner join nods.nods_erp_Bas_Area ebap on eba.ExtrSource = ebap.ExtrSource and eba.ParentID=ebap.AreaID
                            inner join ndw.ndw_AreaProCity apc on eba.ExtrSource = apc.CompanyID and eba.AreaID=apc.AreaID 
                            where eba.ExtrSource = '{UserInfo.UserExtrSource}' and  eba.AllowUsed=1 and eba.Level = 2 ";

                var idArr = DistrictIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var ids = "";
                foreach (var id in idArr)
                {
                    ids += "'" + id + "',";
                }
                ids = ids.Trim(',');

                if (param == 1)
                {
                    sql += $@" and apc.ParentAreaID in({ids}) ";
                }
                else
                {
                    sql += $@" and apc.StockID in({ids}) ";
                }
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnChooseDataTable

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="DistrictID"></param>
        /// <param name="StockID"></param>
        /// <returns></returns>
        public DataTable ReturnChooseDataTable(int param, string DistrictID)
        {
            try
            {
                var sql = $@" select distinct eba.*,ebap.AreaName as ParentName,true as IsChoose
                            from nods.nods_erp_Bas_Area eba
                            inner join nods.nods_erp_Bas_Area ebap on eba.ExtrSource = ebap.ExtrSource and eba.ParentID=ebap.AreaID
                            inner join ndw.ndw_AreaProCity apc on eba.ExtrSource = apc.CompanyID and eba.AreaID=apc.AreaID 
                            where eba.ExtrSource = '{UserInfo.UserExtrSource}' and  eba.AllowUsed=1 and eba.Level = 2 ";
                if (param == 1)
                {
                    sql += $@" and apc.ParentAreaID='{DistrictID}' ";
                }
                else
                {
                    sql += $@" and apc.StockID='{DistrictID}' ";
                }
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region (ok)修改地区所属区域 ModifyOdsErpBasArea

        [JsonType]
        [MustLogin]
        [Description("修改地区所属区域")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyOdsErpBasArea([ParamDescription("城市")] OdsErpBasAreaModel.ModifyAreaModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (param == null) return Error("参数对象为空");
                var cmdList = new List<CommandInfo>();
                //清空
                var cmdDel = new CommandInfo()
                {
                    CommandText = $@" UPDATE ndw.ndw_AreaProCity SET ParentAreaID ='' WHERE  CompanyID = '{UserInfo.UserCompanyId}' and  ParentAreaID='{param.DistrictID}' ",
                };
                cmdList.Add(cmdDel);

                if (param.AreaCodeList != null)
                {
                    foreach (var item in param.AreaCodeList)
                    {
                        //不存在的城市插入
                        var insertCmd = new CommandInfo()
                        {
                            CommandText = $@"
insert into ndw.ndw_AreaProCity(ExtrSource,CompanyID,AreaID,ParentAreaID) 
select '{UserInfo.UserExtrSource}', '{UserInfo.UserCompanyId}','{item}','' 
from nods.nods_erp_Bas_Area A 
where not exists
(
    SELECT 1 FROM ndw.ndw_AreaProCity B WHERE  CompanyID = '{UserInfo.UserCompanyId}' and  A.AreaID = b.AreaID
) 
and A.ExtrSource = '{UserInfo.UserExtrSource}' and A.AreaID = '{item}'",
                        };
                        //更新所选地区
                        cmdList.Add(insertCmd);
                        var updateCmd = new CommandInfo()
                        {
                            CommandText = $" UPDATE ndw.ndw_AreaProCity SET ParentAreaID ='{param.DistrictID}' WHERE CompanyID = '{UserInfo.UserCompanyId}' and  AreaID='{item}' ",
                        };
                        cmdList.Add(updateCmd);
                    }
                }
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region (ok)修改地区所属仓库 ModifyOdsErpBasStock

        [JsonType]
        [MustLogin]
        [Description("修改地区所属仓库")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyOdsErpBasStock([ParamDescription("城市")] OdsErpBasAreaModel.ModifyStockModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                if (param == null) return Error("参数对象为空");
                var cmdList = new List<CommandInfo>();
                //清空
                var cmdDel = new CommandInfo()
                {
                    CommandText = $" UPDATE ndw.ndw_AreaProCity SET StockID ='' " +
                                  $"WHERE CompanyID = '{UserInfo.UserCompanyId}' and  StockID='{param.StockID}' ",
                };
                cmdList.Add(cmdDel);
                if (param.AreaCodeList != null)
                {
                    foreach (var item in param.AreaCodeList)
                    {
                        //不存在的城市插入
                        var insertCmd = new CommandInfo()
                        {
                            CommandText = $@"
insert into ndw.ndw_AreaProCity(ExtrSource,CompanyID,AreaID,StockID) 
select '{UserInfo.UserExtrSource}','{UserInfo.UserCompanyId}','{item}','' from nods.nods_erp_Bas_Area A 
where not exists
(
    SELECT 1 FROM ndw.ndw_AreaProCity B WHERE  CompanyID = '{UserInfo.UserCompanyId}' and  A.AreaID = b.AreaID
)
and  ExtrSource = '{UserInfo.UserExtrSource}' and A.AreaID = '{item}'",
                        };
                        //更新所选地区
                        cmdList.Add(insertCmd);
                        var updateCmd = new CommandInfo()
                        {
                            CommandText = $@" UPDATE ndw.ndw_AreaProCity SET StockID ='{param.StockID}' WHERE  CompanyID = '{UserInfo.UserCompanyId}' and AreaID='{item}' ",
                        };
                        cmdList.Add(updateCmd);
                    }
                }
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region (ok)返回DataTable ReturnAllOdsErpBasStockList

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ReturnAllOdsErpBasStockList()
        {
            var sql = $@" SELECT * FROM nods.nods_erp_Bas_Stock 
where  CompanyID = '{UserInfo.UserCompanyId}' and  (StockCode in ({string.Join(",", STOCKLIST)}) or StockCode like '%YW%')
order by StockCode";
            var cmd = Helper.GetSqlStringCommand(sql);
            return Helper.ExecuteDataTable(cmd);
        }

        #endregion




    }
}
